unit RelatorioComprasPorFornecedor;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, UrelatorioBase, RLPDFFilter, RLXLSFilter, RLFilters,
  RLHTMLFilter, RLDraftFilter, DB, DBTables, RLReport, StdCtrls, ExtCtrls,
  Buttons, Mask, ToolEdit, CurrEdit;

type
  TFRelatorioComprasPorFornecedor = class(TFrelatorioBase)
    rgOrdem: TRadioGroup;
    GroupBox3: TGroupBox;
    Label5: TLabel;
    edDataIni: TDateEdit;
    edDataFim: TDateEdit;
    RLBand2: TRLBand;
    RLBand3: TRLBand;
    RLBand4: TRLBand;
    RLLabel1: TRLLabel;
    RLDBText2: TRLDBText;
    RLLabel2: TRLLabel;
    RLDBText3: TRLDBText;
    RLLabel3: TRLLabel;
    RLDBResult1: TRLDBResult;
    RLLabel4: TRLLabel;
    RLSystemInfo2: TRLSystemInfo;
    GroupBox4: TGroupBox;
    Label4: TLabel;
    edGrupoFornSelecionados: TEdit;
    Label6: TLabel;
    edGrupoFornNaoSelecionados: TEdit;
    Label3: TLabel;
    edQtd: TRxCalcEdit;
    procedure btnPesquisarClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FRelatorioComprasPorFornecedor: TFRelatorioComprasPorFornecedor;

implementation

uses UGeral;

{$R *.dfm}

procedure TFRelatorioComprasPorFornecedor.btnPesquisarClick(Sender: TObject);
const
  ctPesquisa =
    'SELECT %s'#10+
    '  FORNECEDOR.FORN_RAZAO  AS Fornecedor,'#10+
    '  SUM(NOTAFISCAL.NF_VALORNF) AS Total'#10+
    'FROM'#10+
    '  NOTAFISCAL'#10+
    '  INNER JOIN FORNECEDOR ON (FORNECEDOR.FORN_CODIGO = NOTAFISCAL.NF_FORNECEDOR)'#10+
    'WHERE'#10+
    '  (NOTAFISCAL.EXCLUIDO <> ''S'' OR NOTAFISCAL.EXCLUIDO IS NULL) AND'#10+
    '  (NOTAFISCAL.NF_SITUACAO = 1) %s'#10+
    'GROUP BY'#10+
    '  NOTAFISCAL.NF_FORNECEDOR_DESC,'#10+
    '  FORNECEDOR.FORN_RAZAO'#10+
    'ORDER BY'#10+
    '  %s %s';

var
  limite, Filtro, Campo, Ordem : String;

begin

  executaSQL('UPDATE FORNECEDOR SET FORN_GRUPO = '''' WHERE FORN_GRUPO IS NULL');

  Filtro := '';
  limite := '';

  if edDataIni.Date = 0 then
  begin

    ShowMessage('A data inicial deve ser informada!');
    edDataIni.SetFocus;
    Exit;

  end;

  if edGrupoFornSelecionados.Text <> '' then
    Filtro := Filtro + montaFiltroSql('FORN_GRUPO', edGrupoFornSelecionados.Text);

  if edGrupoFornNaoSelecionados.Text <> '' then
    Filtro := Filtro + montaFiltroSql('FORN_GRUPO', edGrupoFornNaoSelecionados.Text, True);

  if edQtd.Value > 0 then
    limite := 'FIRST ' + FloatToStr(edQtd.Value);

  if edDatafim.Date = 0 then
  begin

    ShowMessage('A data final deve ser informada!');
    edDatafim.SetFocus;
    Exit;

  end;

  Filtro := Filtro + ' AND(NOTAFISCAL.NF_DATAESTOQUE BETWEEN ' + QuotedStr(FormatDateTime('mm/dd/yyyy', edDataIni.Date)) + ' AND '
                                                               + QuotedStr(FormatDateTime('mm/dd/yyyy', edDataFim.Date)) + ')';

  case rgOrdem.ItemIndex of
    0:begin

        Campo := 'FORN_RAZAO';
        Ordem := '';

      end;
    1:begin

        Campo := '2';
        Ordem := 'DESC';

      end;
    2:begin

        Campo := '2';
        Ordem := '';

      end;
  end;

  Qpesquisa.Close;
  Qpesquisa.SQL.Clear;
  Qpesquisa.SQL.Add(Format(ctPesquisa, [limite, Filtro, Campo, Ordem]));
  Qpesquisa.Open;

  TCurrencyField(Qpesquisa.FieldByName('Total')).DisplayFormat := '#,##0.00';

  inherited;

end;

procedure TFRelatorioComprasPorFornecedor.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  FRelatorioComprasPorFornecedor.Release;
  FRelatorioComprasPorFornecedor := Nil
end;

end.
